﻿/*******************************************************************************
* Copyright (C) sykjwh.cn
* 
* Author: liuxiang
* Create Date: 2019/04/10
* Description: Automated building by liuxiang20041986@qq.com 
* http://www.sykjwh.cn/
*********************************************************************************/

using System;
using Aspose.Cells;
using System.Data;
using System.Collections;
using System.Reflection;
using System.IO;

namespace Sykj.Infrastructure
{
    /// <summary>
    /// excel操作类
    /// </summary>
    public class AsposeExcel
    {
        private Workbook book = null;
        private Worksheet sheet = null;

        /// <summary>
        /// 导出构造数
        /// </summary>
        public AsposeExcel()
        {
            book = new Workbook();
            sheet = book.Worksheets[0];
        }

        #region Export
        /// <summary>
        /// list导出excel
        /// </summary>
        /// <param name="list">强类型数据源</param>
        /// <param name="outFileName">导出路径</param>
        /// <returns>是否成功</returns>
        public bool Export(IEnumerable list,string outFileName)
        {
            bool yn = false;
            try
            {
                int r = 0;
                //遍历每一行
                foreach (var item in list)
                {
                    //利用反射获得属性的所有公共属性
                    Type modelType = item.GetType();
                    PropertyInfo[] pArr= modelType.GetProperties();

                    //构建表头
                    if (r==0)
                    {
                        for (int col = 0; col < pArr.Length; col++)
                        {
                            Cell cell = sheet.Cells[0, col];
                            cell.PutValue(pArr[col].Name);
                            Style style = book.CreateStyle();
                            style.Font.IsBold = true;
                            cell.SetStyle(style);
                        }
                    }
                    //构造值
                    for (int col = 0; col < pArr.Length; col++)
                    {
                        object value = pArr[col].GetValue(item);
                        if (value.GetType() == typeof(DateTime))
                        {
                            sheet.Cells[r + 1, col].PutValue(Convert.ToDateTime(value).ToString());
                        }
                        else
                        {
                            sheet.Cells[r + 1, col].PutValue(value);
                        }
                    }
                    r++;
                }
                sheet.AutoFitColumns();
                book.Save(outFileName);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
            }
        }

        /// <summary>
        /// list导出excel
        /// </summary>
        /// <param name="list">强类型数据源</param>
        /// <returns>字节数组</returns>
        public byte[] Export(IEnumerable list)
        {
            try
            {
                int r = 0;
                //遍历每一行
                foreach (var item in list)
                {
                    //利用反射获得属性的所有公共属性
                    Type modelType = item.GetType();
                    PropertyInfo[] pArr = modelType.GetProperties();

                    //构建表头
                    if (r == 0)
                    {
                        for (int col = 0; col < pArr.Length; col++)
                        {
                            Cell cell = sheet.Cells[0, col];
                            cell.PutValue(pArr[col].Name);
                            Style style = book.CreateStyle();
                            style.Font.IsBold = true;
                            cell.SetStyle(style);
                        }
                    }
                    //构造值
                    for (int col = 0; col < pArr.Length; col++)
                    {
                        object value = pArr[col].GetValue(item);
                        if (value.GetType() == typeof(DateTime))
                        {
                            sheet.Cells[r + 1, col].PutValue(Convert.ToDateTime(value).ToString());
                        }
                        else
                        {
                            sheet.Cells[r + 1, col].PutValue(value);
                        }
                    }
                    r++;
                }
                sheet.AutoFitColumns();
                MemoryStream stream = book.SaveToStream();
                var buffer = stream.ToArray();
                return buffer;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// datatable导出excel
        /// </summary>
        /// <param name="dt">DataTable数据源</param>
        /// <param name="outFileName">导出路径</param>
        /// <returns>是否成功</returns>
        public bool Export(DataTable dt,string outFileName)
        {
            bool yn = false;
            try
            {
                AddHeader(dt);
                AddBody(dt);

                sheet.AutoFitColumns();
                book.Save(outFileName);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
            }
        }

        /// <summary>
        /// datatable导出excel
        /// </summary>
        /// <param name="dt">DataTable数据源</param>
        /// <returns>字节数组</returns>
        public byte[] Export(DataTable dt)
        {
            try
            {
                AddHeader(dt);
                AddBody(dt);

                sheet.AutoFitColumns();
                MemoryStream stream = book.SaveToStream();
                var buffer = stream.ToArray();
                return buffer;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region Import
        /// <summary>
        /// 文件导入DataTable
        /// </summary>
        /// <param name="inFileName">文件路径</param>
        /// <returns>DataTable</returns>
        public DataTable Import(string inFileName)
        {
            Workbook book = new Workbook(inFileName);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
            // dt_Import.
            return dt_Import;
        }

        /// <summary>
        /// stream导入DataTable
        /// </summary>
        /// <param name="stream">流</param>
        /// <returns>DataTable</returns>
        public DataTable Import(Stream stream)
        {
            Workbook book = new Workbook(stream);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
            // dt_Import.
            return dt_Import;
        }
        #endregion

        #region private
        /// <summary>
        /// 添加头部
        /// </summary>
        /// <param name="dt"></param>
        private void AddHeader(DataTable dt)
        {
            Cell cell = null;
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                cell = sheet.Cells[0, col];
                cell.PutValue(dt.Columns[col].ColumnName);
                Style style = book.CreateStyle();
                style.Font.IsBold = true;
                cell.SetStyle(style);
            }
        }

        /// <summary>
        /// 添加正文
        /// </summary>
        /// <param name="dt"></param>
        private void AddBody(DataTable dt)
        {
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
                }
            }
        }
        #endregion
    }
}